package com.bailugansan.dao.implement;

import com.bailugansan.dao.UserDao;
import com.bailugansan.pojo.Role;
import com.bailugansan.pojo.User;
import com.bailugansan.util.DbUtil;
import com.mysql.jdbc.StringUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author BailuGansan
 */
public class UserDaoImpl implements UserDao {
    @Override
    public User login(Connection conn, String userCode) {
        PreparedStatement prep = null;
        ResultSet resu = null;
        User user = null;

        String sql = "select * from smbms_user where userCode = ?";
        Object[] params = {userCode};

        if(conn != null){
            ResultSet execute = DbUtil.execute(conn, sql, params, resu, prep);
            try {
                if(execute.next()){
                    user = new User();
                    user.setId(execute.getInt("id"));
                    user.setUserCode(execute.getString("userCode"));
                    user.setUserName(execute.getString("userName"));
                    user.setUserPassword(execute.getString("userPassword"));
                    user.setGender(execute.getInt("gender"));
                    user.setBirthday(execute.getDate("birthday"));
                    user.setPhone(execute.getString("phone"));
                    user.setAddress(execute.getString("address"));
                    user.setUserRole(execute.getInt("userRole"));
                    user.setCreatedBy(execute.getInt("createdBy"));
                    user.setCreationDate(execute.getDate("creationDate"));
                    user.setModifyBy(execute.getInt("modifyBy"));
                    user.setModifyDate(execute.getDate("modifyDate"));
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally { // 无论获取是否成功都要释放资源
                DbUtil.closeConn(conn, prep, resu);
            }
        }
        return user;
    }

    @Override
    public int pwdModify(Connection conn, int id, String newPassword) {
        PreparedStatement preparedStatement = null;
        int updateResult = 0;

        String sql = "update smbms_user set userPassword = ? where id = ?";
        Object[] params = {newPassword, id};

        try{
            updateResult = DbUtil.update(conn, sql, params, updateResult, preparedStatement);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            DbUtil.closeConn(conn, preparedStatement, null);
        }
        return updateResult;
    }

    @Override
    public int getUserCount(Connection conn, String userName, int userRole) {
        PreparedStatement prep = null;
        int result = 0;

        StringBuffer sql = new StringBuffer();
        sql.append("select count(*) count from smbms_user su join smbms_role sr on su.userRole = sr.id");

        ArrayList<Object> params = new ArrayList<>();

        // 动态 sql
        if(!StringUtils.isNullOrEmpty(userName)){
            sql.append(" and su.userName like ?");
            params.add("%"+userName+"%");
        }
        if(userRole > 0){
            sql.append(" and su.userRole = ?");
            params.add(userRole);
        }

        ResultSet resultSet = DbUtil.execute(conn, sql.toString(), params.toArray(), null, prep);
        try {
            while(resultSet.next()){
                result = resultSet.getInt("count");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtil.closeConn(null, prep, resultSet);
        }

        return result;
    }

    @Override
    public List<User> getUserList(Connection conn, String userName, int userRole, int currentPage, int pageSize) {
        PreparedStatement prep = null;
        List<User> userList = null;
        ResultSet resultSet = null;

        StringBuffer sql = new StringBuffer();
        sql.append("select * from smbms_user su join smbms_role sr on su.userRole = sr.id");

        ArrayList<Object> params = new ArrayList<>();

        // 动态 sql
        if(!StringUtils.isNullOrEmpty(userName)){
            sql.append(" and su.userName like ?");
            params.add("%"+userName+"%");
        }
        if(userRole > 0){
            sql.append(" and su.userRole = ?");
            params.add(userRole);
        }
        sql.append(" order by su.id");
        sql.append(" limit ?, ?");

        params.add(currentPage);
        params.add(pageSize);

        resultSet = DbUtil.execute(conn, sql.toString(), params.toArray(), resultSet, prep);
        try {
            userList = new ArrayList<>();
            while(resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("su.id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setUserRoleName(resultSet.getString("roleName"));
                userList.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeConn(null, prep,resultSet);
        }

        return userList;
    }

    @Override
    public List<Role> getRoleList(Connection conn) {
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        List<Role> roleList = null;

        String sql = "select * from smbms_role";
        Object[] params = {};

        resultSet = DbUtil.execute(conn, sql, params, resultSet, prep);
        try {
            roleList = new ArrayList<>();
            while(resultSet.next()){
                Role role = new Role();
                role.setId(resultSet.getInt("id"));
                role.setRoleCode(resultSet.getString("roleCode"));
                role.setRoleName(resultSet.getString("roleName"));
                roleList.add(role);
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            DbUtil.closeConn(null, prep, resultSet);
        }

        return roleList;
    }

//    @Test
//    void testGetUserCount(){
//        int res = getUserCount(DbUtil.getConn(), null, 0);
//        System.out.println(res);
//    }

//    @Test
//    void testGetUserList(){
//        List<User> userList = getUserList(DbUtil.getConn(), "赵", 0, 0, 5);
//        for (User user : userList) {
//            System.out.println(user.toString());
//        }
//    }

//    @Test
//    void testGetRoleList(){
//        List<Role> roleList = getRoleList(DbUtil.getConn());
//        for (Role role : roleList) {
//            System.out.println(role.toString());
//        }
//    }
}
